﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceModel.Syndication;
using System.Web;
using System.Text;
using RecruitmentAgency.Models.DataTypes;

namespace RecruitmentAgency.Models.DataProviders
{
    /// <summary>
    /// Provides database access for CV data
    /// </summary>
    public class CvDataProvider : BaseDataProvider, ICvDataProvider
    {
        /// <summary>
        /// Gets an instance of the RecruitmentDatabaseDataContext
        /// </summary>
        private RecruitmentDatabaseDataContext DataContext
        {
            get
            {
                return new RecruitmentDatabaseDataContext();
            }
        }

        private List<string> Cache(string cacheName)
        {
            if (HttpRuntime.Cache[cacheName] != null) return (List<string>)HttpRuntime.Cache[cacheName];
            return null;
        }

        #region Get Drop down list content

        /// <summary>
        /// Gets unique users' skill names from the database
        /// </summary>
        /// <returns>A list of unique users' skill names</returns>
        public List<string> GetUniqueSkillNames()
        {
            if (Cache("skillNames") != null) return Cache("skillNames");
            else
            {
                List<string> skillNames = new List<string>();
                try
                {
                    foreach (up_GetUniqueSkillNamesResult result in DataContext.up_GetUniqueSkillNames())
                    {
                        skillNames.Add(result.SkillName);
                    }
                }
                catch (Exception ex)
                {
                    DataProvider.HandleException(ex);
                }
                if (skillNames.Count > 0) skillNames.Sort();
                HttpRuntime.Cache["skillNames"] = skillNames;
                return skillNames;
            }
        }

        /// <summary>
        /// Gets unique users' qualification type names from the database
        /// </summary>
        /// <returns>A list of unique users' qualification type names</returns>
        public List<string> GetUniqueQualificationTypeNames()
        {
            if (Cache("qualificationTypeNames") != null) return Cache("qualificationTypeNames");
            else
            {
                List<string> qualificationTypeNames = new List<string>();
                try
                {
                    foreach (up_GetUniqueQualificationTypeNamesResult result in DataContext.up_GetUniqueQualificationTypeNames())
                    {
                        qualificationTypeNames.Add(result.QualificationTypeName);
                    }
                }
                catch (Exception ex)
                {
                    DataProvider.HandleException(ex);
                }
                if (qualificationTypeNames.Count > 0) qualificationTypeNames.Sort();
                HttpRuntime.Cache["qualificationTypeNames"] = qualificationTypeNames;
                return qualificationTypeNames;
            }
        }

        /// <summary>
        /// Gets unique users' qualification subject names from the database
        /// </summary>
        /// <returns>A list of unique users' qualification subject names</returns>
        public List<string> GetUniqueQualificationSubjectNames()
        {
            if (Cache("qualificationSubjectNames") != null) return Cache("qualificationSubjectNames");
            else
            {
                List<string> qualificationSubjectNames = new List<string>();
                try
                {
                    foreach (up_GetUniqueQualificationSubjectNamesResult result in DataContext.up_GetUniqueQualificationSubjectNames())
                    {
                        qualificationSubjectNames.Add(result.QualificationSubject);
                    }
                }
                catch (Exception ex)
                {
                    DataProvider.HandleException(ex);
                }
                if (qualificationSubjectNames.Count > 0) qualificationSubjectNames.Sort();
                HttpRuntime.Cache["qualificationSubjectNames"] = qualificationSubjectNames;
                return qualificationSubjectNames;
            }
        }

        /// <summary>
        /// Gets unique users' preferred job sector names from the database
        /// </summary>
        /// <returns>A list of unique users' preferred job sector names</returns>
        public List<string> GetUniqueSectorPreferences()
        {
            if (Cache("sectorPreferenceNames") != null) return Cache("sectorPreferenceNames");
            else
            {
                List<string> sectorPreferenceNames = new List<string>();
                try
                {
                    foreach (up_GetUniqueSectorPreferencesResult result in DataContext.up_GetUniqueSectorPreferences())
                    {
                        sectorPreferenceNames.Add(result.SectorName);
                    }
                }
                catch (Exception ex)
                {
                    DataProvider.HandleException(ex);
                }
                if (sectorPreferenceNames.Count > 0) sectorPreferenceNames.Sort();
                HttpRuntime.Cache["sectorPreferenceNames"] = sectorPreferenceNames;
                return sectorPreferenceNames;
            }
        }

        /// <summary>
        /// Gets all contact preferences from the database
        /// </summary>
        /// <returns>A list of contact preferences</returns>
        public List<string> GetContactPreferences()
        {
            if (Cache("contactPreferences") != null) return Cache("contactPreferences");
            else
            {
                List<string> contactPreferences = new List<string>();
                try
                {
                    foreach (up_GetContactPreferencesResult result in DataContext.up_GetContactPreferences())
                    {
                        contactPreferences.Add(result.ContactPreferenceName);
                    }
                }
                catch (Exception ex)
                {
                    DataProvider.HandleException(ex);
                }
                if (contactPreferences.Count > 0) contactPreferences.Sort();
                HttpRuntime.Cache["contactPreferences"] = contactPreferences;
                return contactPreferences;
            }
        }

        /// <summary>
        /// Gets all types of authority to work from the database
        /// </summary>
        /// <returns>A list of types of authority to work</returns>
        public List<string> GetAuthorityToWork()
        {
            if (Cache("authorityToWorkList") != null) return Cache("authorityToWorkList");
            else
            {
                List<string> authorityToWorkList = new List<string>();
                try
                {
                    foreach (up_GetAuthorityToWorkResult result in DataContext.up_GetAuthorityToWork())
                    {
                        authorityToWorkList.Add(result.Type);
                    }
                }
                catch (Exception ex)
                {
                    DataProvider.HandleException(ex);
                }
                if (authorityToWorkList.Count > 0) authorityToWorkList.Sort();
                HttpRuntime.Cache["authorityToWorkList"] = authorityToWorkList;
                return authorityToWorkList;
            }
        }

        /// <summary>
        /// Gets all titles from the database
        /// </summary>
        /// <returns>List of string titles></returns>
        public List<string> GetTitles()
        {
            if (Cache("titlesList") != null) return Cache("titlesList");
            else
            {
                List<string> titlesList = new List<string>();
                try
                {
                    foreach (up_GetTitlesResult result in DataContext.up_GetTitles())
                    {
                        titlesList.Add(result.Name);
                    }
                }
                catch (Exception ex)
                {
                    DataProvider.HandleException(ex);
                }
                if (titlesList.Count > 0) titlesList.Sort();
                HttpRuntime.Cache["titlesList"] = titlesList;
                return titlesList;
            }
        }

        /// <summary>
        /// Gets all proficiency levels from the database
        /// </summary>
        /// <returns>List of proficiency levels></returns>
        public List<string> GetproficiencyLevels()
        {
            if (Cache("proficiecyLevelList") != null) return Cache("proficiecyLevelList");
            else
            {
                List<string> proficiecyLevelList = new List<string>();
                try
                {
                    foreach (up_GetProficiencyLevelsResult result in DataContext.up_GetProficiencyLevels())
                    {
                        proficiecyLevelList.Add(result.ProficiencyLevel);
                    }
                }
                catch (Exception ex)
                {
                    DataProvider.HandleException(ex);
                }
                if (proficiecyLevelList.Count > 0) proficiecyLevelList.Sort();
                HttpRuntime.Cache["proficiecyLevelList"] = proficiecyLevelList;
                return proficiecyLevelList;
            }
        }

        /// <summary>
        /// Gets all grade choices from the database
        /// </summary>
        /// <returns>All grade choices</returns>
        public List<string> GetGrades()
        {
            if (Cache("gradesList") != null) return Cache("gradesList");
            else
            {
                List<string> gradesList = new List<string>();
                try
                {
                    foreach (up_GetGradesResult gr in DataContext.up_GetGrades())
                    {
                        gradesList.Add(gr.Name);
                    }
                }
                catch (Exception ex)
                {
                    DataProvider.HandleException(ex);
                }
                if (gradesList.Count > 0) gradesList.Sort();
                HttpRuntime.Cache["gradesList"] = gradesList;
                return gradesList;
            }
        }

        /// <summary>
        /// Gets all of the verification type names from the database
        /// </summary>
        /// <returns>All of the verification type names</returns>
        public List<string> GetVerificationTypes()
        {
            if (Cache("verificationTypeList") != null) return Cache("verificationTypeList");
            else
            {
                List<string> verificationTypeList = new List<string>();
                try
                {
                    foreach (up_GetVerificationTypesResult result in DataContext.up_GetVerificationTypes())
                    {
                        verificationTypeList.Add(result.VerificationTypeName);
                    }
                }
                catch (Exception ex)
                {
                    DataProvider.HandleException(ex);
                    
                }
                HttpRuntime.Cache["verificationTypeList"] = verificationTypeList;
                return verificationTypeList;
            }
        }

        /// <summary>
        /// Gets all industry sector names from the database
        /// </summary>
        /// <returns>A list of industry sector names</returns>
        public List<string> GetSectors()
        {
            if (Cache("sectorList") != null) return Cache("sectorList");
            else
            {
                List<string> sectorList = new List<string>();
                try
                {
                    foreach (up_GetSectorsResult result in DataContext.up_GetSectors())
                    {
                        sectorList.Add(result.SectorName);
                    }
                }
                catch (Exception ex)
                {
                    DataProvider.HandleException(ex);
                    
                }
                if (sectorList.Count > 0) sectorList.Sort();
                HttpRuntime.Cache["sectorList"] = sectorList;
                return sectorList;
            }
        }

        /// <summary>
        /// Gets all qualification type choices from the database
        /// </summary>
        /// <returns>List of qualification types</returns>
        public List<string> GetQualificationTypes()
        {
            if (Cache("qualificationTypeList") != null) return Cache("qualificationTypeList");
            else
            {
                List<string> qualificationTypeList = new List<string>();
                try
                {
                    foreach (up_GetQualificationTypesResult result in DataContext.up_GetQualificationTypes())
                    {
                        qualificationTypeList.Add(result.QualificationTypeName);
                    }
                }
                catch (Exception ex)
                {
                    DataProvider.HandleException(ex);
                    
                }
                if (qualificationTypeList.Count > 0) qualificationTypeList.Sort();
                HttpRuntime.Cache["qualificationTypeList"] = qualificationTypeList;
                return qualificationTypeList;
            }
        }

        /// <summary>
        /// Gets all relationship choices from the database
        /// </summary>
        /// <returns>List of relationship choices</returns>
        public List<string> GetRelationships()
        {
            if (Cache("relationshipList") != null) return Cache("relationshipList");
            else
            {
                List<string> relationshipList = new List<string>();
                try
                {
                    foreach (up_GetRelationshipsResult result in DataContext.up_GetRelationships())
                    {
                        relationshipList.Add(result.RelationshipName);
                    }
                }
                catch (Exception ex)
                {
                    DataProvider.HandleException(ex);
                    
                }
                if (relationshipList.Count > 0) relationshipList.Sort();
                HttpRuntime.Cache["relationshipList"] = relationshipList;
                return relationshipList;
            }
        }

        #endregion

        #region Get methods

        /// <summary>
        /// Gets a System.ServiceModel.Syndication.SyndicationFeed full of CV data
        /// </summary>
        /// <param name="homePageUri">The URI of the page to go to when the user clicks the RSS feed title link</param>
        /// <returns>A System.ServiceModel.Syndication.SyndicationFeed full of CV data</returns>
        public SyndicationFeed GetCvRssFeed(string homePageUri)
        {
            List<SyndicationItem> rssItems = new List<SyndicationItem>();
            try
            {
                // create RSS items
                foreach (up_GetCvDataForRssResult result in DataContext.up_GetCvDataForRss())
                {
                    SyndicationItem rssItem = new SyndicationItem();
                    rssItem.Id = "ConnectionsJobSeeker" + result.PersonId;
                    rssItem.Categories.Add(new SyndicationCategory(result.JobSectorPreference ?? "None"));
                    rssItem.LastUpdatedTime = DateTime.Now;
                    rssItem.Links.Add(new SyndicationLink(new Uri(homePageUri + "ViewCv/" + result.PersonId)));
                    rssItem.PublishDate = DateTime.Now;
                    StringBuilder summary = new StringBuilder("Job preference: ");
                    summary.Append(result.JobSectorPreference);
                    summary.Append("<br />");
                    summary.Append(result.NumberOfGcses);
                    summary.Append(result.NumberOfGcses == 1 ? " GCSE" : " GCSEs");
                    summary.Append(" (or equivalent), ");
                    summary.Append(result.NumberOfALevels);
                    summary.Append(result.NumberOfALevels == 1 ? " A-Level and " : " A-Levels and ");
                    summary.Append(result.NumberOfVocationals);
                    summary.Append(" vocational qualification");
                    summary.Append(result.NumberOfVocationals == 1 ? "<br />" : "s<br />");
                    if (result.EmploymentSectors != null)
                    {
                        string[] sectors = result.EmploymentSectors.Split(',');
                        summary.Append("Worked in the ");
                        summary.Append(sectors[0].Trim());
                        if (sectors.Length > 1)
                        {
                            for (int n = 1; n < sectors.Length - 1; n++)
                            {
                                summary.Append(", ");
                                summary.Append(sectors[n].Trim());
                            }
                            summary.Append(" and ");
                            summary.Append(sectors[sectors.Length - 1].Trim());
                            summary.Append(" sectors.");
                        }
                        else summary.Append(" sector.");
                    }
                    rssItem.Summary = SyndicationContent.CreateHtmlContent(summary.ToString());
                    rssItem.Title = SyndicationContent.CreatePlaintextContent(result.FirstName + " " + result.LastName);
                    rssItems.Add(rssItem);
                }
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
                
            }
            // create RSS feed
            SyndicationFeed rssFeed = new SyndicationFeed(rssItems);
            rssFeed.Description = new TextSyndicationContent("You can click on the links to view the job seeker's whole CV at the Connections website.");
            rssFeed.Id = "ConnectionsJobSeekersCvs1";
            rssFeed.LastUpdatedTime = DateTime.Now;
            rssFeed.Links.Add(new SyndicationLink(new Uri(homePageUri)));
            rssFeed.Title = new TextSyndicationContent("Connections job seekers' CVs");
            return rssFeed;
        }

        /// <summary>
        /// Gets a list of PersonEmployment instances that relate to the provided PersonId
        /// </summary>
        /// <param name="personId">The ID number of the user whose employment record is required</param>
        /// <returns>A list of PersonEmployment instances</returns>
        public List<PersonEmployment> GetPersonEmployment(int personId)
        {
            List<PersonEmployment> personEmployments = new List<PersonEmployment>();
            try
            {
                foreach (up_GetEmploymentsResult result in DataContext.up_GetEmployments(personId))
                {
                    personEmployments.Add(new PersonEmployment
                    {
                        CompanyName = result.EmployerName,
                        EmploymentId = result.EmploymentId,
                        EndDate = ((DateTime)result.DateFinished).ToLongDateString(),
                        EmploymentVerificationType = result.VerificationType,
                        IndustrySector = result.SectorName,
                        KeyDuties = result.KeyDuties,
                        PersonId = result.Personid,
                        StartDate = ((DateTime)result.DateStarted).ToLongDateString(),
                        JobTitle = result.JobTitle,
                        EmploymentVerified = result.Verified
                    });
                }
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);                
            }
            return personEmployments;
        }

        /// <summary>
        /// Gets a List of PersonReferee instances that relate to the provided PersonId
        /// </summary>
        /// <param name="personId">The ID number of the user whose referees record is required</param>
        /// <returns>A List of PersonReferee instances</returns>
        public List<PersonReferee> GetPersonReferees(int personId)
        {
            List<PersonReferee> personReferees = new List<PersonReferee>();
            try
            {
                foreach (up_GetRefereesResult result in DataContext.up_GetReferees(personId))
                {
                    personReferees.Add(new PersonReferee
                    {
                        RefereeAddressLine1 = result.AddressLine1,
                        RefereeAddressLine2 = result.AddressLine2 ?? string.Empty,
                        RefereeTown = result.Town,
                        RefereePostCode = result.PostCode,
                        Email = result.Email,
                        Occupation = result.Occupation,
                        PermissionToContact = (bool)result.PermissionToContact,
                        PermissionToStoreDetails = result.PermissionToStoreDetails,
                        PersonId = result.Persons_idUser,
                        RefereePhone = result.ContactPhone,
                        RefereeFirstName = result.Forename,
                        RefereeId = result.RefereesId,
                        RefereeLastName = result.Surname,
                        ReferenceText = result.ReferenceDoc,
                        Relationship = result.Relationship,
                        RefereeTitle = result.TitleName,
                        RefereeVerified = result.Verified,
                        RefereeVerificationType = result.VerificationType,
                        YearsKnown = result.YearsKnown.ToString()
                    });
                }
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
                
            }            
            return personReferees;
        }

        /// <summary>
        /// Gets a List of PersonQualification instances that relate to the provided PersonId
        /// </summary>
        /// <param name="personId">The ID number of the user whose qualification record is required</param>
        /// <returns>A List of PersonQualification instances</returns>
        public  List<PersonQualification> GetPersonQualifications(int personId)
        {
            List<PersonQualification> personQualifications = new List<PersonQualification>();
            try
            {
                foreach (up_GetQualificationsResult pq in DataContext.up_GetQualifications(personId))
                {
                    personQualifications.Add(new PersonQualification
                                                 {
                                                     AwardedBy = pq.AwardingBody,
                                                     QualificationName = pq.QualificationName,
                                                     PersonQualificationId = pq.QualificationId,
                                                     PersonId = pq.Persons_PersonId,
                                                     Result = pq.Result,
                                                     Subject = pq.QualificationSubject,
                                                     Type =  pq.QualificationType,
                                                     QualificationVerified = pq.Verified,
                                                     QualificationVerificationType = pq.VerificationType,
                                                     Vocational = pq.Vocational,
                                                     Year = pq.YearObtained == 0 ? string.Empty : pq.YearObtained.ToString()
                                                 });
                }
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);                
            }            
            return personQualifications.OrderBy(q => q.Year).Reverse().ToList();
        }

        /// <summary>
        /// Gets a List of PersonQualification instances that relate to the provided PersonId
        /// </summary>
        /// <param name="personId">The ID number of the user whose skill record is required</param>
        /// <returns>A List of PersonSkill instances</returns>
        public List<PersonSkill> GetPersonSkills(int personId)
        {
            List<PersonSkill> personSkills = new List<PersonSkill>();
            try
            {
                foreach (up_GetSkillsResult result in DataContext.up_GetSkills(personId))
                {
                    personSkills.Add(new PersonSkill
                                         {
                                             SkillName = result.SkillName,
                                             PersonId = result.Persons_PersonId,
                                             PersonSkillId = result.SkillsId,
                                             ProficiencyLevel = result.ProficiencyLevel,
                                             YearsOfExperience = result.YearOfExperience.ToString(),
                                             SkillVerified = result.Verified,
                                             SkillVerificationType = result.Verified ? result.VerificationType : null
                                         });
                }
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }           
            return personSkills;
        }

        /// <summary>
        /// Gets a PersonalDetail instance that relates to the provided PersonId
        /// </summary>
        /// <param name="personId">The ID number of the user whose personal details are required</param>
        /// <returns>A PersonalDetail instance</returns>
        public PersonalDetail GetPersonalDetail(int personId)
        {
            PersonalDetail person = new PersonalDetail();
            try
            {
                List<up_GetPersonByIdResult> results = DataContext.up_GetPersonById(personId).ToList();
                if (results.Count > 0)
                {
                    up_GetPersonByIdResult result = results[0];
                    person.PersonId = result.PersonId;
                    person.AddressLine1 = result.AddressLine1;
                    person.AddressLine2 = result.AddressLine2;
                    person.Town = result.Town;
                    person.PostCode = result.PostCode;
                    person.AuthorityToWork = result.AutoritiesToworkName;
                    person.ContactPreference = result.ContactPreference;
                    person.JobSectorPreference = result.JobSectorPreference;
                    person.DateOfBirth = !String.IsNullOrEmpty(result.Dob) ? DateTime.Parse(result.Dob).ToLongDateString() : string.Empty;
                    person.Email1 = result.EmailPersonal;
                    person.Email2 = result.EmailWork;
                    person.FirstName = result.FirstName;
                    person.HomePhone = result.Landline;
                    person.LastName = result.LastName;
                    person.MiddleNames = result.MiddleNames;
                    person.MobilePhone = result.Mobile;
                    person.PenaltyPoints = result.PenaltyPoints != null ? (int)result.PenaltyPoints : 0;
                    person.PhotoUrl = result.PhotoUrl ?? string.Empty;
                    person.Sex = result.Female == true ? Sex.Female : Sex.Male;
                    person.Title = result.TitleName;
                    person.UserName = result.Username;
                    person.WebsiteUrl = result.WebsiteUrl;
                    person.RoleId = result.RoleId;
                    person.PersonalDetailVerified = result.Verified;
                    person.PersonalDetailVerificationType = result.VerificationType;
                }
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
            return person;
        }

        /// <summary>
        /// Gets a PersonalDetail instance that relates to the provided username
        /// </summary>
        /// <param name="username">The username of the user whose personal details are required</param>
        /// <returns>A PersonalDetail instance</returns>
        public PersonalDetail GetPersonByUsername(string username)
        {
            PersonalDetail person = new PersonalDetail();
            try
            {
                List<up_GetPersonByUsernameResult> results = DataContext.up_GetPersonByUsername(username).ToList();
                if (results.Count > 0)
                {
                    up_GetPersonByUsernameResult result = results[0];
                    person.PersonId = result.PersonId;
                    person.AddressLine1 = result.AddressLine1;
                    person.AddressLine2 = result.AddressLine2;
                    person.Town = result.Town;
                    person.PostCode = result.PostCode;
                    person.AuthorityToWork = result.AutoritiesToworkName;
                    person.ContactPreference = result.ContactPreference;
                    person.JobSectorPreference = result.JobSectorPreference;
                    person.DateOfBirth = !String.IsNullOrEmpty(result.Dob) ? DateTime.Parse(result.Dob).ToLongDateString() : string.Empty;
                    person.Email1 = person.Email1;
                    person.Email2 = person.Email2;
                    person.FirstName = person.FirstName;
                    person.HomePhone = result.Landline;
                    person.LastName = result.LastName;
                    person.MiddleNames = result.MiddleNames;
                    person.MobilePhone = result.Mobile;
                    person.PenaltyPoints = result.PenaltyPoints != null ? (int)result.PenaltyPoints : 0;
                    person.PhotoUrl = result.PhotoUrl ?? string.Empty;
                    person.Sex = person.Sex;
                    person.Title = result.TitleName;
                    person.UserName = result.Username;
                    person.WebsiteUrl = result.WebsiteUrl;
                    person.RoleId = result.RoleId;
                }
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
            return person;
        }

        /// <summary>
        /// Gets the ID number of the Person that relates to the provided username
        /// </summary>
        /// <param name="username">The username of the user whose personal details are required</param>
        /// <returns>The ID number of the Person that relates to the provided username</returns>
        public int GetPersonIdByUsername(string username)
        {
            int? id = -1;
            try
            {
                DataContext.up_GetPersonIdByUsername(username, ref id);
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
            return id != null ? (int)id : 0;
        }

        #endregion

        #region Add methods

        /// <summary>
        /// Adds a PersonSkill instance to the database
        /// </summary>
        /// <param name="personSkill">The PersonSkill instance to add to the database</param>
        public void AddSkill(PersonSkill personSkill)
        {
            DataContext.up_AddSkill(personSkill.PersonId, personSkill.SkillName, personSkill.ProficiencyLevel, String.IsNullOrEmpty(personSkill.YearsOfExperience) ? 0 : int.Parse(personSkill.YearsOfExperience));
        }

        /// <summary>
        /// Adds a PersonQualification instance to the database
        /// </summary>
        /// <param name="personQualification">The PersonQualification instance to add to the database</param>
        public void AddQualification(PersonQualification personQualification)
        {
            try
            {
                DataContext.up_AddQualification(personQualification.PersonId, personQualification.QualificationName, personQualification.Subject, personQualification.Type, personQualification.Vocational, string.IsNullOrEmpty(personQualification.Year) ? 0 : int.Parse(personQualification.Year), personQualification.QualificationVerified, personQualification.QualificationVerificationType, (!string.IsNullOrEmpty(personQualification.Result) ? personQualification.Result : ""), personQualification.AwardedBy);  
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }                     
        }

        /// <summary>
        /// Adds a PersonReferee instance to the database
        /// </summary>
        /// <param name="personReferee">The PersonReferee instance to add to the database</param>
        public void AddReferee(PersonReferee personReferee)
        {
            try
            {                
                DataContext.up_AddReferee(personReferee.PersonId, personReferee.RefereeTitle, personReferee.RefereeFirstName, personReferee.RefereeLastName, personReferee.Email, personReferee.RefereePhone, personReferee.Relationship, personReferee.PermissionToContact, personReferee.PermissionToStoreDetails, personReferee.RefereeVerified, personReferee.RefereeVerificationType, (!string.IsNullOrEmpty(personReferee.ReferenceText) ? personReferee.ReferenceText : string.Empty), string.IsNullOrEmpty(personReferee.YearsKnown) ? 0 : int.Parse(personReferee.YearsKnown), personReferee.Occupation, personReferee.RefereeAddressLine1, personReferee.RefereeAddressLine2, personReferee.RefereePostCode, personReferee.RefereeTown);
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
            
        }

        /// <summary>
        /// Adds a PersonEmployment instance to the database
        /// </summary>
        /// <param name="personEmployment">The PersonEmployment instance to add to the database</param>
        public void AddEmployment(PersonEmployment personEmployment)
        {
            try
            {
                DataContext.up_AddEmployment(personEmployment.PersonId, DateTime.Parse(personEmployment.StartDate), DateTime.Parse(personEmployment.EndDate), personEmployment.JobTitle, personEmployment.KeyDuties, personEmployment.CompanyName, personEmployment.IndustrySector, personEmployment.EmploymentVerified, personEmployment.EmploymentVerificationType);
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
        }

        #endregion

        #region Update methods

        /// <summary>
        /// Updates the record in the database that relates to the PersonId property of the supplied PersonalDetail instance with the details in the PersonalDetail
        /// </summary>
        /// <param name="personDetail">The PersonalDetail instance to update</param>
        public void UpdatePersonDetails(PersonalDetail personDetail)
        {
            try
            {
                DataContext.up_UpdatePersonalDetails(personDetail.PersonId, personDetail.Title, personDetail.FirstName, personDetail.MiddleNames, personDetail.LastName, personDetail.Email1, (!string.IsNullOrEmpty(personDetail.Email2) ? personDetail.Email2 : ""), personDetail.WebsiteUrl.ToLower().Replace("www.", string.Empty).Replace("http://", string.Empty), personDetail.PhotoUrl, (personDetail.Title.EndsWith("s") ? true : false), personDetail.AuthorityToWork, personDetail.ContactPreference, personDetail.JobSectorPreference, personDetail.MobilePhone, personDetail.HomePhone, personDetail.DateOfBirth == string.Empty ? null : (DateTime.Parse(personDetail.DateOfBirth)).ToShortDateString(), personDetail.PenaltyPoints, personDetail.AddressLine1, personDetail.AddressLine2, personDetail.PostCode, personDetail.Town, personDetail.PersonalDetailVerified, personDetail.PersonalDetailVerificationType); 
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
        }

        /// <summary>
        /// Updates the record in the database that relates to the PersonId property of the supplied PersonEmployment instance with the details in the PersonEmployment
        /// </summary>
        /// <param name="personEmployment">The PersonEmployment instance to update</param>
        public void UpdateEmployment(PersonEmployment personEmployment)
        {
            try
            {
                DataContext.up_UpdateEmployment(personEmployment.EmploymentId, DateTime.Parse(personEmployment.StartDate), DateTime.Parse(personEmployment.EndDate), personEmployment.JobTitle, personEmployment.KeyDuties, personEmployment.CompanyName, personEmployment.IndustrySector, personEmployment.EmploymentVerified, personEmployment.EmploymentVerificationType);
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
        }

        /// <summary>
        /// Updates the record in the database that relates to the PersonId property of the supplied PersonQualification instance with the details in the PersonQualification
        /// </summary>
        /// <param name="personQualification">The PersonQualification instance to update</param>
        public void UpdateQualification(PersonQualification personQualification)
        {
            try
            {
                DataContext.up_UpdateQualification(personQualification.PersonQualificationId, personQualification.QualificationName, personQualification.Subject, personQualification.Type, personQualification.Vocational, string.IsNullOrEmpty(personQualification.Year) ? 0 : int.Parse(personQualification.Year), personQualification.QualificationVerified, personQualification.QualificationVerificationType, personQualification.Result, personQualification.AwardedBy);
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
        }

        /// <summary>
        /// Updates the record in the database that relates to the PersonId property of the supplied PersonReferee instance with the details in the PersonReferee
        /// </summary>
        /// <param name="personReferee">The PersonReferee instance to update</param>
        public void UpdateReferee(PersonReferee personReferee)
        {
            try
            {
                DataContext.up_UpdateReferee(personReferee.RefereeId, personReferee.RefereeTitle, personReferee.RefereeFirstName, personReferee.RefereeLastName, personReferee.Email, personReferee.RefereePhone, personReferee.Relationship, personReferee.PermissionToContact, personReferee.PermissionToStoreDetails, personReferee.RefereeVerified, personReferee.RefereeVerificationType, !string.IsNullOrEmpty(personReferee.ReferenceText) ? personReferee.ReferenceText :"", int.Parse(personReferee.YearsKnown), personReferee.Occupation, personReferee.RefereeAddressLine1, personReferee.RefereeAddressLine2, personReferee.RefereePostCode, personReferee.RefereeTown);
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
        }

        /// <summary>
        /// Updates the record in the database that relates to the PersonId property of the supplied PersonSkill instance with the details in the PersonSkill
        /// </summary>
        /// <param name="personSkill">The PersonSkill instance to update</param>
        public void UpdateSkills(PersonSkill personSkill)
        {
            try
            {
                DataContext.up_UpdateSkills(personSkill.PersonSkillId, personSkill.SkillName, personSkill.ProficiencyLevel, personSkill.SkillVerified, personSkill.SkillVerificationType, string.IsNullOrEmpty(personSkill.YearsOfExperience) ? 0 : int.Parse(personSkill.YearsOfExperience));
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
        }

        #endregion

        #region Delete methods

        /// <summary>
        /// Deletes the person and all related details from the database that relates to the input personId parameter
        /// </summary>
        /// <param name="personId">The ID number of person to delete</param>
        public void DeletePerson(int personId)
        {
            try
            {
                DataContext.up_DeletePerson(personId);
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
        }

        /// <summary>
        /// Deletes the qualification record that relates to the input qualificationId parameter
        /// </summary>
        /// <param name="qualificationId">The ID number of the qualification record to delete</param>
        public void DeleteQualification(int qualificationId)
        {
            try
            {
                DataContext.up_DeleteQualification(qualificationId);
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
        }

        /// <summary>
        /// Deletes the employment record that relates to the input employmentId parameter
        /// </summary>
        /// <param name="employmentId">The ID number of the employment record to delete</param>
        public void DeleteEmployment(int employmentId)
        {
            try
            {
                DataContext.up_DeleteEmployment(employmentId);
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
        }

        /// <summary>
        /// Deletes the skill record that relates to the input skillId parameter
        /// </summary>
        /// <param name="skillId">The ID number of the skill record to delete</param>
        public void DeleteSkill(int skillId)
        {
            try
            {
                DataContext.up_DeleteSkill(skillId);
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
        }

        /// <summary>
        /// Deletes the referee record that relates to the input refereeId parameter
        /// </summary>
        /// <param name="refereeId">The ID number of the referee record to delete</param>
        public void DeleteReferee(int refereeId)
        {
            try
            {
                DataContext.up_DeleteReferee(refereeId);
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
        }

        #endregion

        /// <summary>
        /// Gets the person detail results and paging data
        /// </summary>
        /// <param name="pageSize">The number of rows of results to a page</param>
        /// <param name="pageNumber">The number of the page of results to return</param>
        /// <param name="minimumNumberOfGcse">The minimum number of GCSEs to search for</param>
        /// <param name="minimumNumberOfAlevel">The minimum number of A-Levels to search for</param>
        /// <param name="jobSectorPreference">The job sector preference of the user to search for</param>
        /// <param name="qualificationType">The type of qualification of the user to search for</param>
        /// <param name="qualificationSubject">The qualification subject of the user to search for</param>
        /// <param name="skillName">The name of the skill of the user to search for</param>
        /// <param name="personName">The name of the user to search for</param>
        /// <returns>A CvSearchData instance that holds the person detail results, paging data and the number of records that need verifying for each person</returns>
        public CvSearchData SearchCvs(int pageSize, int pageNumber, int? minimumNumberOfGcse, int? minimumNumberOfAlevel, string jobSectorPreference, string qualificationType, string qualificationSubject, string skillName, string personName)
        {
            CvSearchData searchData = new CvSearchData();
            searchData.PageSize = pageSize;
            searchData.PageNumber = pageNumber;
            try
            {
                foreach (up_SearchCvsResult result in DataContext.up_SearchCvs(pageSize, pageNumber, minimumNumberOfGcse, minimumNumberOfAlevel, jobSectorPreference, qualificationType, qualificationSubject, skillName, personName))
                {
                    SearchResult searchResult = new SearchResult
                    {
                        PersonId = (int) result.PersonId,
                        FirstName = result.FirstName,
                        LastName = result.LastName,
                        HomePhone = result.Landline,
                        MobilePhone = result.Mobile,
                        Email1 = result.EmailPersonal,
                        Email2 = result.EmailWork,
                        ContactPreference = result.ContactPreference,
                        JobSectorPreference = result.JobSectorPreference,
                        Town = result.Town,
                        NotVerifiedCount = (int) result.NotVerifiedCount
                    };
                    searchData.Results.Add(searchResult);
                    searchData.TotalResultCount = (int)result.TotalResultCount;
                }
            }
            catch (Exception ex)
            {
                DataProvider.HandleException(ex);
            }
            return searchData;
        }
    }
}